function f_return_check_result()
{   #如果结果不为空或者第一个位置参数不等于1表示检查不通过, 需要告警提示信息
    declare fun_name
    fun_name="f_return_check_result"
    if [ -n "${1}" ] && [ "${1}x" != "1x" ]
    then
        f_logging "$(eval echo ${log_addr}):REMAIND" "${2}"
        [ "${3}x" != "warnx" ] && all_sql_is_ok=0
    fi
    return 0
}

function f_check_data_type()
{ #检查数据类型是否是mysql支持的数据类型, 通过这个去判断index或者key关键字后面的的字符串是数据类型还是索引名, 主要是判断index是作为字段名还是作为关键字使用
    declare _data_type _check_data_type
    _check_data_type="${1}"
    _data_type="TINYINT|SMALLINT|MEDIUMINT|INT|INTEGER|BIGINT|FLOAT|DOUBLE|DECIMAL"
    _data_type="${_data_type}|DATE|TIME|YEAR|DATETIME|TIMESTAMP"
   _data_type="${_data_type}|CHAR|VARCHAR|TINYBLOB|TINYTEXT|BLOB|TEXT|MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT"
   echo "$(grep -Pwic "${_data_type}" <<< "${_check_data_type}" || true)"
   return 0
}

function f_check_mysql_for_key_word()
{
    #检查是否使用了保留字
    declare _use_word _info _opt _line_tmp __row_tmp _row_tmp check_mysql_state _key_words _sed_count fun_name
    fun_name="f_check_mysql_for_key_word"
    _use_word="$(sed 's/;$//g;s/ *$//g;s/^ *//g' <<< "${1}")" #去掉前后空格
    _info="${2}"
    _opt="${3}"
    _line_tmp="${4}"
    _row_tmp="${5}"
    _sed_count="${6}" #如果存在多个关键字, 从第几个开始加反引号, 比如 add index index(index) 应该--> add index `index`(`index`)  这个值=3
                      #如果存在多个关键字, 从第几个开始加反引号, 比如 add index(index) 应该--> add index `index`(`index`)  这个值=2
    _key_words="GET ADD ALL ALTER ANALYZE AND AS ASC ASENSITIVE BEFORE BETWEEN BIGINT BINARY BLOB BOTH BY CALL CASCADE CASE CHANGE CHAR CHARACTER"
    _key_words="${_key_words} CHECK COLLATE COLUMN CONDITION CONNECTION CONSTRAINT CONTINUE CONVERT CREATE CROSS CURRENT_DATE CURRENT_TIME AFTER"
    _key_words="${_key_words} CURRENT_TIMESTAMP CURRENT_USER CURSOR DATABASE DATABASES DAY_HOUR DAY_MICROSECOND DAY_MINUTE DAY_SECOND DEC FIRST"
    _key_words="${_key_words} DECIMAL DECLARE DEFAULT DELAYED DELETE DESC DESCRIBE DETERMINISTIC DISTINCT DISTINCTROW DIV DOUBLE DROP DUAL"
    _key_words="${_key_words} EACH ELSE ELSEIF ENCLOSED ESCAPED EXISTS EXIT EXPLAIN FALSE FETCH FLOAT FLOAT4 FLOAT8 FOR FORCE FOREIGN FROM"
    _key_words="${_key_words} FULLTEXT GOTO GRANT GROUP HAVING HIGH_PRIORITY HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND IF IGNORE IN INDEX"
    _key_words="${_key_words} INFILE INNER INOUT INSENSITIVE INSERT INT INT1 INT2 INT3 INT4 INT8 INTEGER INTERVAL INTO IS ITERATE JOIN KEY"
    _key_words="${_key_words} KEYS KILL LABEL LEADING LEAVE LEFT LIKE LIMIT LINEAR LINES LOAD LOCALTIME LOCALTIMESTAMP LOCK LONG LONGBLOB"
    _key_words="${_key_words} LONGTEXT LOOP LOW_PRIORITY MATCH MEDIUMBLOB MEDIUMINT MEDIUMTEXT MIDDLEINT MINUTE_MICROSECOND MINUTE_SECOND"
    _key_words="${_key_words} MOD MODIFIES NATURAL NOT NO_WRITE_TO_BINLOG NULL NUMERIC ON OPTIMIZE OPTION OPTIONALLY OR ORDER OUT OUTER"
    _key_words="${_key_words} OUTFILE PRECISION PRIMARY PROCEDURE PURGE RAID0 RANGE READ READS REAL REFERENCES REGEXP RELEASE RENAME REPEAT"
    _key_words="${_key_words} REPLACE REQUIRE RESTRICT RETURN REVOKE RIGHT RLIKE SCHEMA SCHEMAS SECOND_MICROSECOND SELECT SENSITIVE"
    _key_words="${_key_words} SEPARATOR SET SHOW SMALLINT SPATIAL SPECIFIC SQL SQLEXCEPTION SQLSTATE SQLWARNING SQL_BIG_RESULT RANK"
    _key_words="${_key_words} SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT SSL STARTING STRAIGHT_JOIN TABLE TERMINATED THEN TINYBLOB TINYINT"
    _key_words="${_key_words} TINYTEXT TO TRAILING TRIGGER TRUE UNDO UNION UNIQUE UNLOCK UNSIGNED UPDATE USAGE USE USING UTC_DATE UTC_TIME"
    _key_words="${_key_words} UTC_TIMESTAMP VALUES VARBINARY VARCHAR VARCHARACTER VARYING WHEN WHERE WHILE WITH WRITE X509 XOR YEAR_MONTH ZEROFILL"

    check_mysql_state=0
    if [ "$(grep -iwc "${_use_word}" <<< "${_key_words}")x" == "1x" ]
    then
        f_logging "$(eval echo ${log_addr}):ERROR" "提交的sql语句中, 存在mysql的保留字 '${_use_word}', 请勿使用mysql的保留字作为${_info}" "2" "0" "0" >&2|tee -a ${log_file}
        all_sql_is_ok=0
        check_mysql_state=1
    elif [ "$(grep -Pc -- "(-|\.)" <<< "${_use_word}")x" != "0x" ]
    then #还有业务的表名带小数点的, 更恶心, 可以改这里
        if [ "${_opt}x" == "tablex" ]
        then
            f_logging "$(eval echo ${log_addr}):WARN" "提交的sql语句中的表名[${_use_word}], 存在使用中划线或者小数点的现象, 强烈不建议使用!!!" "2" "0" "0" >&2|tee -a ${log_file}
        else
            f_logging "$(eval echo ${log_addr}):WARN" "提交的sql语句中[${line}], 存在使用中划线或者小数点的现象, 强烈不建议使用!!!" "2" "0" "0" >&2|tee -a ${log_file}
        fi
        all_sql_is_ok=0
        check_mysql_state=1
    fi

    #如果使用了关键字, 又是非strict模式, 则将关键字使用反引号引起来
    if [ ${check_mysql_state} -ne 0 ] && [ "${check_mode_opt}x" != "strictx" ] && [ "${_opt}x" == "tablex" ]
    then
        line="$(sed "s/\<${_use_word}\>/\`${_use_word}\`/1" <<< "${_line_tmp}")"
        table_name="\`${table_name}\`"
        table_name="$(sed 's/``/`/g' <<< "${table_name}")" #有可能重复添加反引号
    elif [ ${check_mysql_state} -ne 0 ] && [ "${check_mode_opt}x" != "strictx" ]
    then
        declare __1 __2 __3
        __1="$(grep -icP "^(add|key|index|unique|modify|change|drop|column)$" <<< "${_use_word}")" || true                     #判断是否是这些动作
        __2="$(f_delete_for_string <<< "${_row_tmp}" "nosed"|grep -oiw "${_use_word}"|grep -wc "${_use_word}")" || true #判断关键字的个数
        if [ "$(grep -icP "^(change|modify|add|drop) " <<< "${_row_tmp}")x" == "1x" -a "${__1}x" == "1x" ]
        then #动作是change|modify|add|drop 且关键字是add|key|index|modify|change|drop|column
            #判断这种情况, 1、change 使用add|drop|modify关键字
            #2、add 使用drop|change|modify关键字
            #3、drop 使用add|change|modify关键字
            #4、modify 使用add|drop|change关键字
            if [ "$(grep -c "^${_use_word} " <<< "${_row_tmp}")x" == "1x" ]
            then
                __row_tmp="$(sed "s/\<${_use_word}\>/\`${_use_word}\`/2" <<< "${_row_tmp}")"
                [ ${__2} -gt 2 ] && __row_tmp="$(sed "s/\<${_use_word}\>/\`${_use_word}\`/3" <<< "${__row_tmp}")"
            else
                __row_tmp="$(sed "s/\<${_use_word}\>/\`${_use_word}\`/1" <<< "${_row_tmp}")"
            fi
        elif [ "$(grep -icPw "^(key|index|unique)$" <<< "${_use_word}")x" == "1x" ] && [ "$(grep -icP "^(add|drop) ${_use_word}" <<< "${_row_tmp}")x" == "1x" ]
        then #动作是(add|drop xxx) 且关键字是key|index(xxx表示这个)
            __row_tmp="$(sed "s/\<${_use_word}\>/\`${_use_word}\`/2" <<< "${_row_tmp}")"
           [ ${__2} -gt 2 ] && __row_tmp="$(sed "s/\<${_use_word}\>/\`${_use_word}\`/3" <<< "${__row_tmp}")"
        elif [ "$(grep -icPw "^(column)$" <<< "${_use_word}")x" == "1x" ] && [ "$(grep -icP "^(add|change|drop) ${_use_word}" <<< "${_row_tmp}")x" == "1x" ]
        then #关键字是column, 动作是add|change|drop column
            __row_tmp="$(sed "s/\<${_use_word}\>/\`${_use_word}\`/2" <<< "${_row_tmp}")"
            [ ${__2} -gt 2 ] && __row_tmp="$(sed "s/\<${_use_word}\>/\`${_use_word}\`/3" <<< "${__row_tmp}")"
        else #其他
            __row_tmp="$(sed "s/\<${_use_word}\>/\`${_use_word}\`/1" <<< "${_row_tmp}")"
        fi

        __1="$(grep -iP "^add (index|key|column|unique)" <<< "${_row_tmp}")" || true
        __2="$(awk '{print $3}' <<< "${_row_tmp}"|awk -F'(' '{print $1}')"
        __2="$(f_check_data_type "${__2}")"                                 #判断第三个单词是不是数据类型
        __3="$(awk '{print $4}' <<< "${_row_tmp}"|awk -F'(' '{print $1}')"
        __3="$(f_check_data_type "${__3}")"                                 #判断第四个单词是不是数据类型

        if [ -n "${__1}" ] && [ "${__2}x" == "1x" ] && [ "${__3}x" == "0x" ]
        then #如果第三个单词是数据类型, 就将第二个单词作为列名, 但是第四个单词不能是数据类型  add column int int
            __1="$(awk '{print $2}' <<< "${_row_tmp}")"
            __row_tmp="$(sed "s/\<${__1}\>/\`${__1}\`/1" <<< "${__row_tmp}")"
        elif [ -n "${__1}" ] && [ "${__2}x" == "1x" ]
        then #如果第三个单词是数据类型, 就将第二个单词作为列名 add column int
            __1="$(awk '{print $3}' <<< "${_row_tmp}")"
            __row_tmp="$(sed "s/\<${__1}\>/\`${__1}\`/1" <<< "${__row_tmp}")"
        fi

        unset __1 __2 __3

        __row_tmp="$(sed 's/``/`/g' <<< "${__row_tmp}")"
        line="$(sed "s#${_row_tmp}#${__row_tmp}#1" <<< "${_line_tmp}")"
    fi
    [ -n "${__row_tmp}" ] && row="${__row_tmp}"
    line="$(sed 's/``/`/g' <<< "${line}")"
    return 0
}

function f_check_big_query()
{   # 检查当前是否有大查询, 超过10秒的都算
    declare _opt_ loop _check_t is_long_query fun_name
    fun_name="f_check_big_query"
    _opt_="${1}"
    _check_t="${2}"
    for ((loop=0;loop<10;loop++))
    do
        is_long_query="select * from ${isdbp} where state not like 'Master has sent all binlog to slave%' and (db = '${mysql_db}' or db is null)"
        is_long_query="${is_long_query} and user != 'replica' and time > 10 and info like '%${_check_t}%'"
        is_long_query="$(f_exe_sql_in_mysql "${is_long_query}" "" "" "" "$(eval echo ${log_addr})"|grep -w "${mysql_db}"|grep -w "${_check_t}")" || true
        [ -z "${is_long_query}" ] && loop="null" && break
        sleep 1
    done

    if [ "${loop}x" != "nullx" ]
    then
        is_long_query="select * from ${isdbp} where state not like 'Master has sent all binlog to slave%' and (db = '${mysql_db}' or db is null)"
        { f_exe_sql_in_mysql "${is_long_query} and user != 'replica' and time > 10 and info like '%${_check_t}%'" "" "" "" "$(eval echo ${log_addr})"|grep -w "${mysql_db}"|grep -w "${_check_t}";} || true
        __="检查到 '${mysql_host}:${mysql_port}' '${mysql_db}.${_check_t}' 长时间有超过10s的大查询, ${_opt_}操作失败"
        f_logging "$(eval echo ${log_addr}):WARN" "${__}" "2"
        exe_sql_get_error_info_list+=("${__} ] [ ${line}") #加到这个数组, 目的是都检查完了以后将错误日志打印出来, 防止后面的日志冲刷前面的错误日志
        return 1
    fi
    return 0
}

function f_check_character()
{   #判断字段和表的字符集是否一致
    declare _tmp_table_ _tmp_sql table_character character_is_diff c_diff table_character_list
    declare -a character_is_diff_info
    declare -A char_to_table
    _test_table="${1}"
    _tmp_sql="select concat(TABLE_NAME,':',TABLE_COLLATION) from ${isdbt} where TABLE_NAME in ('${_test_table}') and TABLE_SCHEMA = '${test_db}'"
    table_character_list=($(f_exe_sql_in_test_mysql "${_tmp_sql}" "" "$(eval echo ${log_addr})")) || true

    for table_character in ${table_character_list[@]}
    do
        _tmp_table_="$(awk -F: '{print $1}' <<< "${table_character}")"
        table_character="$(awk -F: '{print $2}' <<< "${table_character}"|awk -F_ '{print $1}')"
        char_to_table["${_tmp_table_}"]="${table_character}"
        if [ "$(grep -ic "^utf8.*" <<< "${table_character}")x" == "0x" ]
        then
            character_is_diff_info+=("${_tmp_table_}:${table_character}")
        fi
    done
    [ -n "${character_is_diff_info}" ] && f_return_check_result "${table_character}" "表 [${character_is_diff_info[*]}] 的字符集非'utf8' 或者 'utf8mb4', 建议修改"
    unset character_is_diff_info
    _tmp_sql="select concat(TABLE_NAME,'.',COLUMN_NAME) from (select it.TABLE_NAME, concat(ic.COLUMN_NAME,':',ic.COLLATION_NAME)COLUMN_NAME,
        if (ic.COLLATION_NAME = it.TABLE_COLLATION,1,0) res from ${isdbc} ic join ${isdbt} it where ic.TABLE_SCHEMA = it.TABLE_SCHEMA 
        and ic.table_name = it.table_name and ic.COLLATION_NAME is not null and ic.TABLE_NAME in ('${_test_table}') 
        and ic.TABLE_SCHEMA = '${test_db}')tmp where res = 0"
    character_is_diff=($(f_exe_sql_in_test_mysql "${_tmp_sql}" "" "$(eval echo ${log_addr})"|awk '{split($i,a,"_");print a[1]}')) || true 
    for c_diff in ${character_is_diff[@]}
    do
        _tmp_table_="$(awk -F'.' '{print $1}' <<< "${c_diff}")"
        character_is_diff_info+=("[${_tmp_table_}:${char_to_table[${_tmp_table_}]} vs ${c_diff}]")
    done
    f_return_check_result "${c_diff}" "sql语句中,部分字段'${character_is_diff_info[*]}'与表的字符集不一致"
    return 0
}

function f_check_col_comment()
{   #检查没有注释的字段
    declare is_col_not_comment _tmp_sql _tmp_show_info
    _test_table="${1}"
    _tmp_sql="select concat(TABLE_NAME,'.',COLUMN_NAME) from ${isdbc} where TABLE_SCHEMA = '${test_db}'"
    _tmp_sql="${_tmp_sql} and TABLE_NAME in ('${_test_table}') and length(COLUMN_COMMENT)=0;"
    is_col_not_comment=($(f_exe_sql_in_test_mysql "${_tmp_sql}" "" "$(eval echo ${log_addr})"|awk '{split($i,a,".");t=a[1];if(res[t]){res[t]=res[t]","a[2]}else{res[t]=a[2]}}
        END{for (i in res){print "["i,res[i]"],"}}'|tr -d "\n"|sed 's/,$//g')) || true #列没有注释
    _tmp_show_info="表 '${_test_table}' 存在部分字段 '${is_col_not_comment[*]}' 没有列注释, 请使用 'comment' 属性添加注释"
    f_return_check_result "${is_col_not_comment}" "${_tmp_show_info}"
    return 0
}

function f_check_default()
{   #检查默认值是null的字段
    declare _filter_sql is_defaults_null _tmp_sql _tmp_show_info
    _test_table="${1}"
    _filter_sql="${2}"
    _tmp_sql="select concat(TABLE_NAME,'.',COLUMN_NAME) from ${isdbc} where TABLE_SCHEMA = '${test_db}' and TABLE_NAME in ('${_test_table}')"
    _tmp_sql="${_tmp_sql} and COLUMN_DEFAULT is NULL and COLUMN_KEY not in ('PRI', 'UNI') ${_filter_sql};"
    is_defaults_null=($(f_exe_sql_in_test_mysql "${_tmp_sql}" "" "$(eval echo ${log_addr})"|awk '{split($i,a,".");t=a[1];if(res[t]){res[t]=res[t]","a[2]}else{res[t]=a[2]}}
        END{for (i in res){print "["i,res[i]"],"}}'|tr -d "\n"|sed 's/,$//g')) || true # 字段默认值不能是null
    _tmp_show_info="表 '${_test_table}' 存在部分字段 '${is_defaults_null[*]}' 默认值为null, 请使用 default 'value' 属性为该列添加默认值"
    f_return_check_result "${is_defaults_null}" "${_tmp_show_info}"
    return 0
}

function f_check_dup_index()
{   #检查重复的索引, 这里的变量命名有点简单, 不能见名知意, 主要是想让一行命令更短
    declare _i _t _g _a _p _test_table fun_name
    fun_name="f_check_dup_index"
    _test_table="$(sed "s/'//g" <<< "${1}")"
    _p="pt-duplicate-key-checker" #工具名
    _t="--port=${test_port} --host=${test_host} --user='${test_user}' --password='${test_pass}'" #参数拼接
    _t="${_t} --databases='${test_db}' --tables='${_test_table}'"                                #参数拼接
    _g="# To remove this duplicate index, execute:"                                              #过滤条件
    _a="部分表存在重复索引, 索引名是: "                                                          #提示信息
    exe_state="0"
    _i="$(eval ${_p} ${_t}|grep -A 1 "${_g}"|sed 's/`//g'|awk -v v="${_a}" 'BEGIN{printf v}/ALTER TABLE/{str="";str=(str""$6);split($3,tt,".");
        t=tt[2];sub(";",",",str);a[t]=a[t]""str;}END{for (i in a){printf "["i ":" a[i]"],"}}'|sed 's/,$//g')" || exe_state="${?}"
    if [ -n "${_i}" ] && [ ${exe_state} -eq 0 ] && [ "${check_mode_opt}x" == "strictx" ]
    then
        all_sql_is_ok=0
    fi
    [ -n "${_i}" ] && [ ${exe_state} -eq 0 ] && f_logging "$(eval echo ${log_addr}):REMAIND" "${_i}"
    return 0
}

function f_check_engine()
{   #检查引擎是否是innodb
    declare table_engine _tmp_sql
    _test_table="${1}"
    _tmp_sql="select if(ENGINE='InnoDB',1,0) from ${isdbt} where TABLE_SCHEMA = '${test_db}' and TABLE_NAME in ('${_test_table}');"
    table_engine="$(f_exe_sql_in_test_mysql "${_tmp_sql}" "" "$(eval echo ${log_addr})")" || true
    f_return_check_result "${table_engine}" "表 '${_test_table}' 使用的引擎非innodb, 请检查"
    return 0
}

function f_check_foreign_key()
{ #检查外键约束及搭建外键约束环境
    declare _table_name _sql is_has_fk _frole __frole fun_name exe_state
    fun_name="f_check_foreign_key"
    _table_name="${1}"
    _frole="${2}"    #可选值是'父表'和'子表', 表示检查是否有父表, 检查是否有子表
    __frole="${3}"
    if [ "${_frole}x" == "父表x" ]
    then
        _sql="select concat(REFERENCED_TABLE_NAME,':',TABLE_NAME,';') from ${isdbkcu} 
            WHERE REFERENCED_TABLE_NAME IS NOT NULL AND table_schema = '${mysql_db}';"
    else
        _sql="select concat(TABLE_NAME,':',REFERENCED_TABLE_NAME,';') from ${isdbkcu} WHERE REFERENCED_TABLE_NAME IS NOT NULL AND table_schema = '${mysql_db}';"
    fi
    
    if [ "${#fk_info["${__frole}"]}x" == "0x" ]
    then
        fk_info["${__frole}"]="$(${mysql_comm} -NBe "${_sql}" 2>&1|grep -P "[a-zA-Z0-9_]"|sed '/Using a password/d'|tr "\n" " ")" && exe_state="${?}" || exe_state="${?}"
    fi

    if [ "${#fk_info["${__frole}"]}x" == "0x" ]
    then #结果为空直接返回
        return 0
    fi
    
    is_has_fk=($(tr " " "\n" <<< "${fk_info["${__frole}"]}"|grep ":${_table_name};"|awk -F: '{print $1}'))
    if [ "${#is_has_fk}x" == "0x" ]
    then #结果为空直接返回
        return 0
    fi
    
    f_logging "$(eval echo ${log_addr}):ERROR" "${mysql_host}:${mysql_port} 的 ${mysql_db}.${_table_name} 表存在外键约束, ${_frole}为: ${mysql_db}.'${is_has_fk[*]}'"
    for fkt in ${is_has_fk[@]}
    do
        get_p_table_sql="$(${mysql_comm} -NBe "show create table ${_mysql_db_}.\`${fkt}\`\G" 2>&1|sed '/Using a password/d'|awk '{if (NR>2) print $0}')"
        f_exe_sql_in_test_mysql "show tables" "" "$(eval echo ${log_addr})"|grep -q "${fkt}" && continue || true
        if [ -n "${get_p_table_sql}" ]
        then
            exe_state="0" 
            f_exe_sql_in_test_mysql "drop table if exists \`${fkt}\`;${get_p_table_sql}" "" "$(eval echo ${log_addr})" || exe_state="${?}"
            if [ "${mode_opt}x" == "alter_tablex" ] || [ "${mode_opt}x" == "create_tablex" ]
            then
                f_exe_sql_in_test_mysql "drop table if exists \`${fkt}\`;${get_p_table_sql}" "${test_db_exe}" "$(eval echo ${log_addr})" || exe_state="${?}"
            fi

            if [ ${exe_state} -ne 0 ]
            then
                f_logging "$(eval echo ${log_addr}):ERROR" "'${test_host}:${test_port}' 创建${_frole} '${get_p_table_sql}' 失败" "2"
                return 1
            else
                f_logging "$(eval echo ${log_addr}):INFO" "'${test_host}:${test_port}' 创建${_frole} '${get_p_table_sql}' 成功"
            fi
        fi
    done
    return 0
}

function f_check_index_info()
{   #检查索是否有索引名
    [ "${execute}x" == "1x" ] && return 0
    unset index_list is_not_index_name index_name _sql _info fun_name
    fun_name="f_check_index_info"
    _test_table="${1}"
    index_list=($(f_exe_sql_in_test_mysql "show index from ${test_db}.${_test_table}" "" "$(eval echo ${log_addr})"|awk '{if ($3==$5)print $3}')) || true

    for index_name in ${index_list[@]}
    do 
        _sql="show create table ${test_db}.${_test_table}"
        is_not_index_name="$(f_exe_sql_in_test_mysql "${_sql}\G" "" "$(eval echo ${log_addr})"|sed 's/`//g'|grep -w KEY|awk -v n="${index_name}" '{if ($2==n)print $0 }')" || true
        _info="检测到添加索引的时候没有指定索引名(或者索引名与字段名一致), 请为该索引指定一个索引名称 '${is_not_index_name}'"
        f_return_check_result "${is_not_index_name}" "${_info}"
    done

    [ -n "${index_list}" ] && f_logging "$(eval echo ${log_addr}):REMAIND" "建议 : 普通索引使用 'ik_xxx' , 唯一索引使用 'uk_xxx' 这种命名习惯"

    #检查索索引名是否使用到了保留字
    index_list=($(f_exe_sql_in_test_mysql "show index from ${test_db}.${_test_table}" "" "$(eval echo ${log_addr})"|awk '{print $3}')) || true
    for index_name in ${index_list[@]}
    do 
        [ "${index_name}x" == "PRIMARYx" ] && continue
        f_check_mysql_for_key_word "${index_name}" "索引名" "col" "${_test_table} 表的索引名" "${index_name}"
    done
    return 0
}

function f_check_integer()
{   #检查整形类型是否是无符号的
    declare is_res_info is_res_info1 is_res_info2 is_res_info3 no_unsigned_list _tmp_sql
    _test_table="${1}"
    _filter_sql="${2}"
    _tmp_sql="select concat(TABLE_NAME,'.',COLUMN_NAME,':',if (DATA_TYPE='int',1,(if(DATA_TYPE='bigint',1,0))),':',"
    _tmp_sql="${_tmp_sql} if(COLUMN_TYPE like '%int%unsigned%',1,0)) from ${isdbc} where TABLE_SCHEMA = '${test_db}'"
    _tmp_sql="${_tmp_sql} and TABLE_NAME in ('${_test_table}') and DATA_TYPE in ('tinyint','int','smallint','mediumint','bigint') ${_filter_sql}"
    is_res_info=($(f_exe_sql_in_test_mysql "${_tmp_sql}" "" "$(eval echo ${log_addr})"|awk '{split($i,a,".");t=a[1];if(res[t]){res[t]=res[t]","a[2]}else{res[t]=a[2]}}
        END{for (i in res){print "["i,res[i]"],"}}'|tr -d "\n"|sed 's/,$//g'))
    for is_res_info_tmp in ${is_res_info[@]}
    do
        is_res_info1="$(awk -F: '{print $1}' <<< "${is_res_info_tmp}")"
        is_res_info2="$(awk -F: '{print $2}' <<< "${is_res_info_tmp}")"
        is_res_info3="$(awk -F: '{print $3}' <<< "${is_res_info_tmp}")"
        [ "${is_res_info3}x" == "0x" ] && no_unsigned_list="${no_unsigned_list} ${is_res_info1}"
    done

    no_unsigned_list="$(sed 's/^ //g' <<< "${no_unsigned_list}")"
    if [ -n "${no_unsigned_list}" ]
    then #允许是无符号, 但是给一个提示
        f_return_check_result "0" "表 '${_test_table}' 的字段 '${no_unsigned_list}' 数据类型是整型, 但不是无符号, 建议添加 'unsigned'" "warn"
    fi
    return 0
}

function f_check_null()
{   #检查允许为null的字段
    declare _filter_sql is_null _tmp_sql
    _test_table="${1}"
    _filter_sql="${2}"
    _tmp_sql="select concat(TABLE_NAME,'.',COLUMN_NAME) from ${isdbc} where TABLE_SCHEMA = '${test_db}' and TABLE_NAME in ('${_test_table}') 
        and IS_NULLABLE='YES' and COLUMN_KEY != 'UNI' ${_filter_sql};"
    is_null=($(f_exe_sql_in_test_mysql "${_tmp_sql}" "" "$(eval echo ${log_addr})"|awk '{split($i,a,".");t=a[1];if(res[t]){res[t]=res[t]","a[2]}else{res[t]=a[2]}}
        END{for (i in res){print "["i,res[i]"],"}}'|tr -d "\n"|sed 's/,$//g')) || true  #是否允许为null
    f_return_check_result "${is_null}" "表 '${_test_table}' 存在部分字段 '${is_null[*]}' 允许为null, 请为该列设置 'not null'"
    return 0
}

function f_check_pk()
{   #检查主键信息, 包含是否有主键, 主键是否是整型, 是否无符号, 是否自增
    declare is_pk_info _tmp_sql _tmp_show_info
    _test_table="${1}"
    _tmp_sql="select concat(TABLE_NAME,'.',COLUMN_NAME),if (DATA_TYPE='int',1,(if(DATA_TYPE='bigint',1,0))),if(COLUMN_TYPE like '%int%unsigned%',1,0),"
    _tmp_sql="${_tmp_sql} if(EXTRA='auto_increment',1,0) from ${isdbc} where TABLE_SCHEMA = '${test_db}'"
    _tmp_sql="${_tmp_sql} and TABLE_NAME in ('${_test_table}') and COLUMN_KEY = 'PRI';"
    is_pk_info=($(f_exe_sql_in_test_mysql "${_tmp_sql}" "" "$(eval echo ${log_addr})"|awk '{split($i,a,".");t=a[1];if(res[t]){res[t]=res[t]","a[2]}else{res[t]=a[2]}}
        END{for (i in res){print "["i,res[i]"],"}}'|tr -d "\n"|sed 's/,$//g')) || true #主键信息
    if [ -z "${is_pk_info}" ]
    then
        f_return_check_result "${#is_pk_info[@]}" "表 '${_test_table}' 没有主键字段, 请添加主键"
    else
        f_return_check_result "${is_pk_info[1]}" "表 '${_test_table}' 的主键 '${is_pk_info[0]}' 数据类型不是整型, 请使用 'int|bigint'"
        f_return_check_result "${is_pk_info[2]}" "表 '${_test_table}' 的主键 '${is_pk_info[0]}' 数据类型是整型, 但不是无符号, 请添加 'unsigned'"
        f_return_check_result "${is_pk_info[3]}" "表 '${_test_table}' 的主键 '${is_pk_info[0]}' 不是自增属性, 请添加 'auto_increment'"
    fi
    return 0
}

function f_check_tab_comment()
{   #检查表是否有注释
    declare is_table_comment _tmp_sql
    _test_table="${1}"
    _tmp_sql="select if(length(TABLE_COMMENT)>0,1,0) from ${isdbt} where TABLE_SCHEMA = '${test_db}' and TABLE_NAME in ('${_test_table}');"
    is_table_comment="$(f_exe_sql_in_test_mysql "${_tmp_sql}" "" "$(eval echo ${log_addr})"|awk '{split($i,a,".");t=a[1];if(res[t]){res[t]=res[t]","a[2]}else{res[t]=a[2]}}
        END{for (i in res){print "["i,res[i]"],"}}'|tr -d "\n"|sed 's/,$//g')" || true
    f_return_check_result "${is_table_comment}" "表 '${_test_table}' 没有表注释, 请使用 'comment' 属性为表添加注释"
    return 0
}

function f_check_table_name_is_use()
{   #检查表是否存在
    [ "${execute}x" == "1x" ] && return 0
    declare _tmp_sql table_is_use_mark fun_name
    fun_name="f_check_table_name_is_use"
    _test_table="${1}"
    _tmp_sql="select TABLE_NAME from ${isdbt} where TABLE_SCHEMA = '${mysql_db}' and TABLE_NAME in ('${_test_table}');"
    table_is_use_mark="$(f_exe_sql_in_mysql "${_tmp_sql}" "" "" "" "$(eval echo ${log_addr})" 2>/dev/null|tr "\n" " "|{ grep -Pi "[a-z]" || true;})" && exe_state="${?}" || exe_state="${?}"
    if [ -n "${table_is_use_mark}" -a ${exe_state} -eq 0 ]
    then
        f_logging "$(eval echo ${log_addr}):ERROR" "表 [${table_is_use_mark}' 在${mysql_host}:${mysql_port}已经存在" "2"
        return 1
    fi
    return 0
}

function f_check_table_size()
{ #检查是否是大表, 超过500G的表就需要dba干预
    declare _check_t_ __ fun_name table_row
    fun_name="f_check_table_size"
    _check_t_="${1}"
    table_size="select concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024),':::',TABLE_ROWS) from ${isdbt}"
    table_size="$(f_exe_sql_in_mysql "${table_size} where table_schema='${mysql_db}' AND table_name='${_check_t_}'" "" "" "" "$(eval echo ${log_addr})")"

    table_row="$(awk -F::: '{print $2}' <<< "${table_size}")"
    table_size="$(awk -F::: '{print $1}' <<< "${table_size}")"

    if [ "$(echo "${table_size}"|sed 's/[0-9.]//g')x" == "0x" ] || [ -z "${table_size}" ]
    then
        __="获取 '${mysql_host}:${mysql_port}' 实例的表 '${mysql_db}.${_check_t_}' 大小异常"
        [ "${execute}x" != "1x" ] && exe_sql_get_error_info_list+=("${__}") && continue
        f_logging "$(eval echo ${log_addr}):ERROR" "${__}" "2" "1"
    fi

    f_logging "$(eval echo ${log_addr}):REMAIND" "'${mysql_host}:${mysql_port}' 实例的表 '${mysql_db}.${_check_t_}' 大小 '${table_size}MB' , 数据行 '${table_row}'" "execute:${execute}"
    # 如果大于500GB, 则需要dba干预
    is_big_table="$(awk -v max_row="${global_max_table_row}" -v max_size="${global_max_table_size}" '{if ($1 >= max_size * 10) {print 1} else if($2 > max_row) {print 2}else if($1 > max_size) {print 3}else{print 0}}' <<< "${table_size} ${table_row}")" 

    if [ "${is_big_table}x" == "1x" ]
    then
        __="'${mysql_host}:${mysql_port}'实例的表'${mysql_db}.${_check_t_}'当前大小是 [$((${table_size}/1024))GB : '${table_row}'], 大于$((${global_max_table_size}*10/1024))GB, "
        if [ "${mysql_big_table_mark}x" == "truex" ]
        then
            f_logging "$(eval echo ${log_addr}):ERROR" "${__} 但是使用了 '-b ${mysql_big_table_mark}' 选项进行改表" "execute:${execute}"
        else
            return__="${__} 检测到目标表过大, 请联系dba处理(在管理机执行成功后让用户重新提交 : cd /opt/soft/zzonlineddl && bash main.sh -a big_table -P ${mysql_port})"
            return 1
        fi
    elif [ "${is_big_table}x" == "2x" ] && [ "${execute_opt}x" == "1x" ]
    then
        __="'${mysql_host}:${mysql_port}'实例的表'${mysql_db}.${_check_t_}'当前行数是 ['${table_row}'], 大于${global_max_table_row}行, "
        if [ $(date +%H) -ge 0  ] && [ $(date +%H) -le 6 ]
        then
            f_logging "$(eval echo ${log_addr}):ERROR" "${__} 但是当前时间是0-6点，可以正常改表" "execute:${execute}"
        elif [ "${mysql_big_table_mark}x" == "truex" ]
        then
            f_logging "$(eval echo ${log_addr}):ERROR" "${__} 但是使用了 '-b ${mysql_big_table_mark}' 选项进行改表" "execute:${execute}"
        else
            return__="${__} 检测到目标表行数过大，请使用定时工单放在0-6点期间进行处理，或者联系DBA处理(在管理机执行成功后让用户重新提交 : cd /opt/soft/zzonlineddl && bash main.sh -a big_table -P ${mysql_port})."
            return 1
        fi
    elif [ "${is_big_table}x" == "3x" ] && [ "${execute_opt}x" == "1x" ]
    then
        __="'${mysql_host}:${mysql_port}'实例的表'${mysql_db}.${_check_t_}'当前大小/行数是 [$((${table_size}/1024))GB : '${table_row}'], 大于50GB, "
        if [ $(date +%H) -ge 0  ] && [ $(date +%H) -le 6 ]
        then
            f_logging "$(eval echo ${log_addr}):ERROR" "${__} 但是当前时间是0-6点，可以正常改表" "execute:${execute}"
        elif [ "${mysql_big_table_mark}x" == "truex" ]
        then
            f_logging "$(eval echo ${log_addr}):ERROR" "${__} 但是使用了 '-b ${mysql_big_table_mark}' 选项进行改表" "execute:${execute}"
        else
            return__="${__} 检测到目标表过大，请使用定时工单放在0-6点期间进行处理，或者联系DBA处理(在管理机执行成功后让用户重新提交 : cd /opt/soft/zzonlineddl && bash main.sh -a big_table -P ${mysql_port})."
            return 1
        fi
    fi
    return 0
}

function f_check_tmp_table()
{   #检查当前环境是否存在临时表, 如果已经存在就退出改表, 一般来讲都不会存在, 因为每次改表失败都会清理临时表
    declare _T _t is_has_tmp_table fun_name
    fun_name="f_check_tmp_table"
    _T="${1}"
    _t="${2}"
    if [ "${_T}x" == "gh-ost" ]
    then
        _t="_${_t}_ghc"
    else
        _t="_${_t}_new"
    fi
    is_has_tmp_table="$(f_exe_sql_in_mysql "select count(*) from ${isdbt} where table_schema = '${mysql_db}' and table_name = '${_t}'" "" "" "" "$(eval echo ${log_addr})")"
    if [ "${is_has_tmp_table}x" != "0x" ]
    then
        f_logging "$(eval echo ${log_addr}):ERROR" "${mysql_host}:${mysql_port} 'db:${mysql_db}' 临时表 '${_t}' 已经存在" "2" "1"
    fi
    return 0
}

function f_check_table_for_batch()
{   #检查mysql诸多规范操作
    [ "${execute}x" == "1x" ] && return 0
    _test_table="${1}"
    declare check_var f_check_list
    f_check_list=(f_check_dup_index f_check_character f_check_null f_check_default
        f_check_col_comment f_check_tab_comment f_check_pk f_check_integer f_check_engine)
    for check_var in ${f_check_list[@]}
    do
        ${check_var} "${_test_table}" &
    done
    wait
    return 0
}

function f_check_mysql_status()
{
    declare fun_name
    fun_name="f_check_mysql_status"
    mysql_comm="mysql --default-character-set=utf8 -u ${mysql_user} -p${mysql_pass} -h ${mysql_host} -P ${mysql_port}"
    #获取mysql的版本, 不通的版本的测试环境不一样, 8.0的使用3872, 8.0以前的使用20001
    if [ "${mode_opt}x" == "crontab_dropx" ] || [ "${mode_opt}x" == "crontab_taskx" ]
    then
        mysql_version="5.6"
    else
        mysql_version="$(timeout ${exe_time} ${mysql_comm} -NBe "select version();" 2>&1|sed '/Using a password/d')" && exe_state="${?}" || exe_state="${?}"
    fi
    if [ -z "${mysql_version}" ] || [ ${exe_state} -ne 0 ]
    then
        mysql_version="$(timeout ${exe_time} ${mysql_comm} -NBe "select version();" 2>&1)" && exe_state="${?}" || exe_state="${?}"
        f_logging "$(eval echo ${log_addr}):ERROR" "'${mysql_host}:${mysql_port}',获取mysql版本号异常:${mysql_version}" "2" "1"
    fi

    is_master="$(timeout ${exe_time} ${mysql_comm} -e "show slave status\G" 2>&1|sed '/Using a password/d'|wc -l)" && exe_state="${?}" || exe_state="${?}"
    if [ "${mode_opt}x" == "crontab_dropx" ] || [ "${mode_opt}x" == "crontab_taskx" ]
    then
        true
    elif [ "${is_master}x" != "0x" ] && [ "${mysql_role}x" == "slavex" ]
    then
        __="在'${dbadb_host}:${dbadb_port}' 获取 '${mysql_port}' 实例的master节点ip是 '${mysql_host}', 但是在'${mysql_host}:${mysql_port}' 查询到复制信息"
        f_logging "$(eval echo ${log_addr}):WARN" "${__}, 这可能是个slave节点, 但是使用了 '-r slave' 继续改表" "2"
    elif [ "${is_master}x" != "0x" ]
    then #如果从dbadb取出的master存在复制信息将终止改表任务, 需要dba干预
        { timeout ${exe_time} ${mysql_comm} -e "show slave status\G" 2>&1|sed '/Using a password/d'|tee -a ${log_file};} || true
        __="$(timeout ${exe_time} ${mysql_comm} -e "show slave status\G" 2>/dev/null|grep -c "_Running: Yes")" || true
        if [ "$(grep -cP "^(5\.1|5\.0)" <<< "${mysql_version}")x" == "1x" ] && [ "${__}x" != "2x" ]
        then
            __="在'${dbadb_host}:${dbadb_port}' 获取 '${mysql_port}' 实例的master节点ip是 '${mysql_host}', 但是在'${mysql_host}:${mysql_port}'"
            __="${__}能查询到复制信息, 但是 是${mysql_version}版本, 且复制信息异常, 可能是切换以后没有清理复制信息, 所以就不管啦"
            f_logging "$(eval echo ${log_addr}):WARN" "${__}" "2"
        else
            __="在'${dbadb_host}:${dbadb_port}' 获取 '${mysql_port}' 实例的master节点ip是 '${mysql_host}', 但是在'${mysql_host}:${mysql_port}'"
            __="${__}能查询到复制信息, 这可能是个slave节点, 改表失败"
            f_logging "$(eval echo ${log_addr}):ERROR" "${__}" "2" "1"
        fi
    fi

    if [ -z "${mysql_slave_info}" ]
    then
        f_logging "$(eval echo ${log_addr}):WARN" "没有检查到 '${mysql_port}' 的 slave ip列表 " "2"
        if [ "$(grep -cP "^(5\.(6|7)|8)" <<< "${mysql_version}")x" == "1x" ]
        then
            f_logging "$(eval echo ${log_addr}):INFO" "现在尝试通过 '${mysql_port}' 的 master节点获取slave列表 "
            exe_state=0
            mysql_slave_info=($(timeout ${exe_time} ${mysql_comm} -NBe "show slave hosts;" 2>&1|sed '/Using a password/d'|awk '{print $2}')) || exe_state="${?}"
            if [ ${exe_state} -ne 0 ]
            then
                f_logging "$(eval echo ${log_addr}):ERROR" "尝试通过 '${mysql_port}' 的 master节点获取slave列表失败, 访问master节点'${mysql_host}:${mysql_port}'异常 "
            elif [ -z "${mysql_slave_info}" ]
            then
                f_logging "$(eval echo ${log_addr}):WARN" "尝试通过 '${mysql_port}' 的 master节点获取slave列表失败, 可能是个单点或者MGR集群 "
            else
                mysql_slave_host="${mysql_slave_info}"
            fi
        fi
    fi

    return 0
}

function f_prepare_mysqltest_env()
{
    declare fun_name
    fun_name="f_prepare_mysqltest_env"
    if [ "$(grep -c "^8" <<< "${mysql_version}")x" == "1x" ]
    then #8.0测试环境
        test_host="${mysql80_host}"               #有删除表的动作, 一定不要将线上环境的地址配上
        test_port="${mysql80_port}"               #不要和线上的一致
        path="/usr/local/mysql80/bin:${path}"
        export PATH="${path}:${PATH}"             #重新加载PATH
    elif [ "$(grep -c "^5\.7" <<< "${mysql_version}")x" == "1x" ]
    then #5.7测试环境
        test_host="${mysql57_host}"               #有删除表的动作, 一定不要将线上环境的地址配上
        test_port="${mysql57_port}"               #不要和线上的一致
        path="/usr/local/mysql57/bin:${path}"
        export PATH="${path}:${PATH}"             #重新加载PATH
    else #8.0以前的版本的测试环境
        test_host="${mysql56_host}"               #有删除表的动作, 一定不要将线上环境的地址配上
        test_port="${mysql56_port}"               #不要和线上的一致
    fi  
    
    #测试环境要求正常, 否则终止改表
    test_mysql_comm="mysql --default-character-set=utf8 -u ${test_user} -p${test_pass} -h ${test_host} -P ${test_port}"
    ${test_mysql_comm} -e "select 1" >/dev/null 2>&1 && exe_state="${?}" || exe_state="${?}"
    if [ "${mode_opt}x" == "crontab_dropx" ] || [ "${mode_opt}x" == "crontab_taskx" ]
    then 
        true
    elif [ ${exe_state} -ne 0 ]
    then 
        f_logging "$(eval echo ${log_addr}):ERROR" "连接测试环境 '${test_host}:${test_port}' 异常" "2" "1"
    fi  
    test_db="alterdb"              #不要和线上的一致最终会被命名为 'alterdb_19216811_3306_12371', 后面三段分别对应master的ip, 端口, 当前改表脚本的PID
    test_db="${test_db}_$(awk -F. '{print $1$2$3$4}' <<< "${mysql_host}")_${mysql_port}_${_PPID}"
    test_db_exe="${test_db}_ptosc"
    test_db="${test_db}_ghost"
    
    __="create database if not exists ${test_db};create database if not exists ${test_db_exe};" #临时变量
    
    timeout ${exe_time} ${test_mysql_comm} -NBe "${__}" 2>&1|sed '/Using a password on /d' && exe_state="${?}" || exe_state="${?}"
    
    if [ ${exe_state} -ne 0 ]
    then
        f_logging "$(eval echo ${log_addr}):ERROR" "在 '${test_host}:${test_port}' 环境创建测试库失败" "2" "1"
    fi

    f_logging "$(eval echo ${log_addr}):INFO" "在 '${test_host}:${test_port}' 环境创建测试库完成 'testdb : ${test_db}'" "execute:${execute}"
    return 0
}


